<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of CompetitionModel
 *
 * @author Marko
 */
class Competition_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    /**
     * Checks if identical competition already inserted.
     * @param type $name
     * @param type $city
     * @param type $country
     * @param type $facility
     * @param type $pool
     * @param type $start_date
     * @param type $end_date
     * @return mixed competition id if exists, false if not
     */
    public function exists_competition($name, $city, $country, $facility, $pool, $start_date, $end_date) {
        $this->db->select('id');
        $this->db->from('competitions');
        $this->db->where('name', $name);
        $this->db->where('city', $city);
        $this->db->where('country', $country);
        $this->db->where('facility', $facility);
        $this->db->where('pool', $pool);
        $this->db->where('start_date', $start_date);
        $this->db->where('end_date', $end_date);

        $query = $this->db->get();
        if ($query->num_rows() == 0)
            return false;
        return $query->row(0)->id;
    }

    /**
     * Counts results for competition.
     * @param type $id
     * @return int
     */
    function count_results($id) {
        $this->db->where('competition_id', $id);
        $this->db->from('results');
        return $this->db->count_all_results();
    }

    /**
     * Selects the maximum year from competitions. If competitions month is later 
     * than september, adds one more year. Used for selecting seasons.
     * @return type
     */
    public function getMaxDate() {
        $query = $this->db->query("SELECT CASE WHEN MONTH(max(start_date)) > 8 THEN (YEAR(max(start_date))+1) ELSE YEAR(max(start_date)) END as start_date FROM competitions");
        return $query->row(0)->start_date;
    }

    /**
     * Select maximum or minimum competition start date from database. Corrects
     * wrong parameters.
     * @param type $date
     * @return type
     */
    public function getDate($date = 'min') {
        $date = $date == 'min' ? 'min' : 'max';
        $this->db->select("$date(YEAR(start_date)) as start_date")->from('competitions');
        return $this->db->get()->row(0)->start_date;
    }

    /**
     * Fetches competitions for the given year.
     * @param type $year
     * @param type $withCountResults
     * @return array result array
     */
    public function fetchByYear($year, $withCountResults = FALSE) {
        $this->db->select('`id`,`name`,`city`, `country`, `facility`,`start_date`,`end_date`,`pool`');
        $this->db->from('competitions');
        $this->db->where("YEAR(start_date)", "$year", false);
        $this->db->order_by('`start_date`', 'desc');
        $competitions = $this->db->get()->result('Competition');
        if ($withCountResults) {
            foreach ($competitions as &$c) {
                $c->setCountResults($this->count_results($c->getId()));
            }
        }
        return $competitions;
    }

    /**
     * Fetches competitions for period given with min and max date. Used for seasons.
     * @param type $min
     * @param type $max
     * @param type $withCountResults
     * @return array result array
     */
    public function fetchBySeason($min, $max, $withCountResults = FALSE) {
        $this->db->select('`id`,`name`,`city`, `country`, `facility`,`start_date`,`end_date`,`pool`');
        $this->db->from('competitions');
        $this->db->where("start_date BETWEEN ", "DATE('$min-09-01') AND DATE('$max-08-31')", false);
        $this->db->order_by('`start_date`', 'desc');
        $competitions = $this->db->get()->result('Competition');
        if ($withCountResults) {
            foreach ($competitions as &$c) {
                $c->setCountResults($this->count_results($c->getId()));
            }
        }
        return $competitions;
    }

    /**
     * Fetches all competitions.
     * @param type $withCountResults
     * @return type
     */
    public function fetchAll($withCountResults = FALSE) {
        $this->db->select('`id`,`name`,`city`, `country`, `facility`,`start_date`,`end_date`,`pool`');
        $this->db->from('competitions');
        $this->db->order_by('`start_date`', 'desc');
        $competitions = $this->db->get()->result('Competition');
        if ($withCountResults) {
            foreach ($competitions as &$c) {
                $c->setCountResults($this->count_results($c->getId()));
            }
        }
        return $competitions;
    }

    /**
     * Fetches competition for id.
     * @param type $id
     * @return Competition
     */
    public function fetch($id) {
        $this->db->select('`id`,`name`,`city`, `country`, `facility`,`start_date`,`end_date`,`pool`');
        $this->db->from('competitions');
        $this->db->where('id', $id);
        return $this->db->get()->row(0, 'Competition');
    }
    
    public function delete($id) {
        $this->db->where('id', $id);
        $this->db->delete('competitions');
    }

    /**
     * Fetches minimum event data for competition.
     * @param type $competition_id
     * @return array result array
     */
    public function fetch_short_events($competition_id) {
        $this->db->select('`id`, `nr`, `distance`, `stroke`, `relay`, `gender`');
        $this->db->from('events');
        $this->db->where('competition_id', $competition_id);
        $this->db->order_by('`gender`', 'asc');
        $this->db->order_by('`nr`', 'asc');
        $this->db->group_by('nr');
        $events = $this->db->get()->result('Event');

        return $events;
    }

    /**
     * Generates html for splits to show in the competition summary table.
     * @param type $split_id
     * @param type $distance
     * @param type $result
     * @param type $result_ms
     * @param type $pool
     * @return string
     */
    public function generateSplitContent($split_id, $distance, $result, $result_ms, $pool) {
        if ($split_id == 0) {
            return '';
        }
        $splits = $this->db->get_where('splits', array('id' => $split_id))->row_array();
        $html = '<table class=\'splits-container\'><tr><td><table class=\'splits table\'><tbody>';
        $total_time = 0;
        $second_table = false;
        for ($i = 1; $i <= 30 && $i * $pool * 2 < $distance; $i++) {
            if ($splits["split_$i"] != 0) {
                $split_ms = $splits["split_$i"];
                $time = $this->Utils->ms_to_result_string($split_ms);
                $split_time = $this->Utils->ms_to_result_string($split_ms - $total_time);
                $total_time = $split_ms;
                if ($result_ms != MAX_INT) {
                    $last_split = $this->Utils->ms_to_result_string($result_ms - $split_ms);
                }
            } else {
                $split_time = '---';
                $time = '---';
            }
            $dist = $i * $pool * 2;

            $html .= '<tr>';
            if ($dist == 100 || $dist == 200 || $dist == 400 || $dist == 800) {
                $html .= "<td><strong>$dist m</strong></td><td><strong>$time</strong></td><td>$split_time</td>";
            } else {
                $html .= "<td>$dist m</td><td>$time</td><td>$split_time</td>";
            }
            $html .= '</tr>';

            if ($i > 15 && !$second_table) {
                $second_table = true;
                $html .= '</tbody></table></td><td><table class=\'splits table\'><tbody>';
            }
        }
        if (!isset($last_split))
            $last_split = '---';
        $html .= '<tr>';
        $html .= "<td>$distance m</td><td>$result</td><td>$last_split</td>";
        $html .= '</tr>';
        $html .= "</tbody></table></td></tr></table>";
        return $html;
    }

    /**
     * Fetches data to display in competition summary table.
     * @param type $competition_id
     * @param type $context team abbreviation, supports only 'tuk'
     * @return mixed result array or false if competition with given id does not exist
     */
    public function fetchEventsResults($competition_id, $context) {
        $this->db->select('`id`, `raw`, `nr`, `distance`, `stroke`, `relay`, `age`, `low_age`, `high_age`, `gender`, `competition_id`, `type`');
        $this->db->from('events');
        $this->db->where('competition_id', $competition_id);
        $this->db->order_by('`nr`', 'asc');
        $this->db->order_by('low_age', 'asc');
        $events = $this->db->get()->result('Event');
        $data['events'] = $events;
        $this->db->select('`id`, CASE exhibition WHEN 1 THEN CONCAT("X", `result`) ELSE `result` END as result, `result_ms`, `distance`, `stroke`, `team`, 
        `seed`, `fina`, `athlete_id`, `relay_id`, `event_id`, `competition_id`, exhibition, split_id', false);
        $this->db->from('results');
        $this->db->where('competition_id', $competition_id);
        if ($context == 'tuk') {
            $this->db->like('team', 'Tartu Ujumisklubi');
        }
        $this->db->order_by('`exhibition`', 'asc');
        $this->db->order_by('`result_ms`', 'asc');
        $results = $this->db->get()->result('Result');
        $pool = $this->fetch($competition_id)->pool;
        foreach ($results as &$result) {
            $result->split_content = $this->generateSplitContent($result->getSplitID(), $result->getDistance(), $result->getResult(), $result->getResultMS(), $pool);
        }

        $data['results'] = $results;
        if (empty($data['events']) && empty($data['results']))
            return FALSE;

        return $data;
    }

    /**
     * Counts athletes for competition
     * @param type $competition_id
     * @param type $context team abbreviation, supports only 'tuk'
     * @return int
     */
    public function count_athletes($competition_id, $context) {

        if ($context == 'tuk') {
            $this->db->like('team', 'Tartu Ujumisklubi');
        }
        $this->db->distinct()->select('athlete_id')->from('results')->where('competition_id', $competition_id);
        return $this->db->get()->num_rows();
    }

    /**
     * Counts teams for competition.
     * @param type $competition_id
     * @return int
     */
    public function count_teams($competition_id) {
        return $this->db->distinct()->select('team')->from('results')->where('competition_id', $competition_id)->get()->num_rows();
    }
    
    /**
     * @todo Fetch top results for competition based on birth date or age.
     * @param type $competition_id
     */
    public function fetch_top_results($competition_id, $gender, $min_year, $max_year) {
        $query = $this->db->query("SELECT athletes.id as athlete_id, result_ms,fina,first_name,last_name,distance,stroke,team,YEAR(dob) as birthyear FROM competitions, results, athletes 
            WHERE YEAR(athletes.dob) BETWEEN $min_year AND $max_year AND
            competitions.id = $competition_id AND results.competition_id = competitions.id AND results.athlete_id = athletes.id AND UPPER(athletes.gender) = UPPER('$gender') 
                ORDER BY fina DESC LIMIT 5");
        return $query->result();
    }

    public function getAthleteYear($competition_id, $date = 'min') {
        $date = $date == 'min' ? 'min' : 'max';
        $this->db->select("$date(YEAR(dob)) as year")->from('competitions, athletes, results');
        $this->db->where('athlete_id = athletes.id AND results.athlete_id = athletes.id');
        $this->db->where('competitions.id', $competition_id);
        $this->db->where('results.competition_id', $competition_id);
        return $this->db->get()->row(0)->year;
    }

    /**
     * Checks if competition exists, if not inserts new competition to database.
     * @param type $name
     * @param type $location
     * @param type $start_date
     * @param type $end_date
     * @param type $pool
     */
    function insertNewCompetition($name, $city, $country, $facility, $pool, $start_date, $end_date) {
        $id = $this->exists_competition($name, $city, $country, $facility, $pool, $start_date, $end_date);
        if ($id === false) {
            $data = array(
                'name' => $name,
                'city' => $city,
                'country' => $country,
                'facility' => $facility,
                'start_date' => $start_date,
                'end_date' => $end_date,
                'pool' => $pool
            );
            $this->db->insert('competitions', $data);
            return $this->db->insert_id();
        } else {
            return $id;
        }
    }

    //-------------------------Deprecated functions---------------------------//

    /**
     * Selects competition data from MDB database.
     * @deprecated since version 1
     * @param type $mdb MS Access Database connection
     * @return Competition
     */
    public function competition_from_mdb($mdb) {
        $competition = $mdb->query("SELECT `Meet_name` as name, `Meet_location` as facility, `Meet_start` as start_date, `Meet_end` as end_date FROM Meet")->row(0, 'Competition');
        $competition->setStartDate(date('Y-m-d', strtotime($competition->getStartDate())));
        $competition->setEndDate(date('Y-m-d', strtotime($competition->getEndDate())));
        $competition->setPool($mdb->query("SELECT Switch(Res_course = 'L',50,Res_course = 'S', 25) AS pool FROM Results GROUP BY Res_course")->row()->pool);
        Utils::utf8_encode_deep($competition);
        return $competition;
    }

}

?>
